package exttable.dbclasses;

import dbutils.DbUtils;
import dbutils.StringQueries;
import exttable.view.ILocalsation;
import utils.Localisation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: ADMIN
 * Date: 13.12.2010
 * Time: 15:58:32
 * To change this template use File | Settings | File Templates.
 */
public class Phase implements ILocalsation {
    private String phaseId, nameRu, nameEn, nameKk;
    private List<Group> groupList;

    public String getPhaseId() {
        return phaseId;
    }

    public void setPhaseId(String phaseId) {
        this.phaseId = phaseId;
    }

    public String getNameRu() {
        return nameRu;
    }

    public void setNameRu(String nameRu) {
        this.nameRu = nameRu;
    }

    public String getNameEn() {
        return nameEn;
    }

    public void setNameEn(String nameEn) {
        this.nameEn = nameEn;
    }

    public String getNameKz() {
        return nameKk;
    }

    public void setNameKk(String nameKk) {
        this.nameKk = nameKk;
    }

    public List<Group> getGroupList() {
        return groupList;
    }

    public void setGroupList(List<Group> groupList) {
        this.groupList = groupList;
    }

    public Phase() {
        groupList = new ArrayList<Group>();
    }

    @Override
    public String toString() {
//        return nameRu;
        return Localisation.getMsg(this);
    }

    public static List<Phase> getPhaseList() {
        int z = 0;
        List<Phase> phaseList = new ArrayList<Phase>();

        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement("select * from dic_phase order by name_en");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Phase phase = new Phase();
                phase.setPhaseId(rs.getString(1));
                phase.setNameRu(rs.getString(2));
                phase.setNameEn(rs.getString(3));
                phase.setNameKk(rs.getString(4));
                phase.setGroupList(Phase.getGroupList(phase.getPhaseId()));
                phaseList.add(phase);

            }
            conn.close();
        } catch (Exception exp) {
            exp.printStackTrace();
        }
        return phaseList;
    }

    public static List<Group> getGroupList(String phaseId) {
        PreparedStatement ps;
        List<Group> groupList = new ArrayList<Group>();
        try {

            Connection conn = DbUtils.getDBConnectionGeneral();
            ps = conn.prepareStatement("select * from groups where phase_id=?");
            ps.setString(1, phaseId);
//            System.out.println("ps : "+ps.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Group group = new Group();
                group.setGroupId(Integer.parseInt(rs.getString(1)));
                group.setGroupCode(rs.getString(2));
//                group.setProbationerList(SelectGroupAndProbationerForm.getProbationerListByGroup(group.getGroupId()));
                groupList.add(group);

            }
        } catch (Exception exp) {
            exp.printStackTrace();
        }

        return groupList;
    }

    public static List<Probationer> getProbationerListByPhase(String phaseId) {
        PreparedStatement ps;
        List<Probationer> probationerList = new ArrayList<Probationer>();
        try {

            Connection conn = DbUtils.getDBConnectionGeneral();
            ps = conn.prepareStatement("SELECT * FROM probationers" +
                    " INNER JOIN groups ON (probationers.group_id=groups.group_id)" +
                    " INNER JOIN dic_phase ON (groups.phase_id = dic_phase.phase_id)" +
                    " WHERE dic_phase.phase_id=? ORDER BY probationer_code");
            ps.setString(1, phaseId);
//            System.out.println("ps : "+ps.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Probationer probationer = new Probationer();
                probationer.setProbationer_id(Integer.parseInt(rs.getString("probationer_id")));
                probationer.setProbationer_code(rs.getString("probationer_code"));
                probationer.setGroup_id(Integer.parseInt(rs.getString("group_id")));
                probationerList.add(probationer);
            }
            conn.close();
            rs.close();
            ps.close();
        } catch (Exception exp) {
            exp.printStackTrace();
        }

        return probationerList;
    }

    public static List<String> getTestNameListByPhase(String phaseId) {
        PreparedStatement ps;
        List<String> testList = new ArrayList<String>();
        try {

            Connection conn = DbUtils.getDBConnectionGeneral();
            ps = conn.prepareStatement(StringQueries.TESTS_BY_PHASE.replace("$phaseVar",phaseId));
//            ps.setString(1, phaseId);
//            System.out.println("ps : "+ps.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                testList.add(rs.getString("analysis_name"));
            }
            conn.close();
            rs.close();
            ps.close();
        } catch (Exception exp) {
            exp.printStackTrace();
        }

        return testList;
    }
}
